Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Configuration.ConfigurationSettings

Public Class DataAccess
    Public Shared Function GetConnectionString(ByVal conName As String) As String
        'variable to hold our connection string for returning it
        Dim strReturn As New String("")
        'check to see if the user provided a connection string name
        'this is for if your application has more than one connection string
        If Not String.IsNullOrEmpty(conName) Then
            'a connection string name was provided
            'get the connection string by the name provided
            strReturn = ConfigurationManager.ConnectionStrings(conName).ConnectionString
        Else
            'no connection string name was provided
            'get the default connection string
            strReturn = ConfigurationManager.ConnectionStrings("CnString").ConnectionString
        End If
        'return the connection string to the calling method
        Return strReturn
    End Function

    Public Shared Function GetClientCount(ByVal storeProc As String, _
                                            ByVal campo As String, _
                                            ByVal criterio As String) As UInt16
        'Create the objects we need to insert a new record
        Dim cnGetClientes As New SqlConnection(GetConnectionString("CnString"))
        Dim cmdGetClientes As New SqlCommand

        Dim Contador As Int16 = 0
        Dim sSQL As New String("")

        criterio = IIf(criterio = "Todos", "", criterio)
        'set the timeout of the SqlCommandObject
        cmdGetClientes.CommandTimeout = 240
        'Set the stored procedure we're going to execute
        sSQL = storeProc
        'Clear any parameters
        cmdGetClientes.Parameters.Clear()
        Try
            'Set the SqlCommand Object Properties
            With cmdGetClientes
                'Tell it what to execute
                .CommandText = sSQL
                'Tell it its a stored procedure
                .CommandType = CommandType.StoredProcedure
                .Parameters.AddWithValue("@Campo", campo)
                .Parameters.AddWithValue("@Criterio", criterio)
                'Set the connection of the object
                .Connection = cnGetClientes
            End With
            cnGetClientes.Open()
            Contador = Convert.ToInt16(cmdGetClientes.ExecuteScalar())
        Catch ex As Exception
            'MsgBox(ex.Message, "Error")
            Return 0
        Finally
            'Now close the connection
            HandleConnection(cnGetClientes)
        End Try
        Return Contador
    End Function


    Public Shared Function GetBindingSource(ByVal cmd As SqlCommand) As BindingSource
        'declare our binding source
        Dim oBindingSource As New BindingSource()
        ' Create a new data adapter based on the specified query.
        Dim daGet As New SqlDataAdapter(cmd)
        ' Populate a new data table and bind it to the BindingSource.
        Dim dtGet As New DataTable()
        'set the timeout of the SqlCommandObject
        cmd.CommandTimeout = 240
        dtGet.Locale = System.Globalization.CultureInfo.InvariantCulture
        Try
            'fill the DataTable with the SqlDataAdapter
            daGet.Fill(dtGet)
        Catch ex As Exception
            'check for errors
            Console.WriteLine("Error in GetBindingSource: " + ex.Message)
            Return Nothing
        End Try
        'set the DataSource for the BindingSource to the DataTable
        oBindingSource.DataSource = dtGet
        'return the BindingSource to the calling method or control
        Return oBindingSource
    End Function


    Public Shared Function GetListaClientes(ByVal storeProc As String, _
                                            ByVal indice As Integer, _
                                            ByVal records As Integer, _
                                            ByVal campo As String, _
                                            ByVal criterio As String) As BindingSource

        'Create the objects we need to insert a new record
        Dim cnGetClientes As New SqlConnection(GetConnectionString("CnString"))
        Dim cmdGetClientes As New SqlCommand

        Dim sSQL As New String("")
        'declare our binding source
        Dim oBindingSource As New BindingSource()
        ' Create a new data adapter based on the specified query.
        Dim daGet As New SqlDataAdapter()
        ' Populate a new data table and bind it to the BindingSource.
        Dim dtGet As New DataTable()

        criterio = IIf(criterio = "Todos", "", "%" + criterio)
        'set the timeout of the SqlCommandObject
        cmdGetClientes.CommandTimeout = 240
        dtGet.Locale = System.Globalization.CultureInfo.InvariantCulture
        'Set the stored procedure we're going to execute
        sSQL = storeProc
        'Clear any parameters
        cmdGetClientes.Parameters.Clear()
        Try
            'Set the SqlCommand Object Properties
            With cmdGetClientes
                'Tell it what to execute
                .CommandText = sSQL
                'Tell it its a stored procedure
                .CommandType = CommandType.StoredProcedure
                'Now add the parameters to our procedure
                .Parameters.AddWithValue("@index", indice)
                .Parameters.AddWithValue("@numRegs", records)
                .Parameters.AddWithValue("@Campo", campo)
                .Parameters.AddWithValue("@Criterio", criterio)
                'Set the connection of the object
                .Connection = cnGetClientes
            End With
            daGet.SelectCommand = cmdGetClientes
            'fill the DataTable with the SqlDataAdapter
            daGet.Fill(dtGet)
        Catch ex As Exception
            Console.WriteLine("GetListaClientes: " + ex.Message)
            Return Nothing
        Finally
            'Now close the connection
            HandleConnection(cnGetClientes)
        End Try
        'set the DataSource for the BindingSource to the DataTable
        oBindingSource.DataSource = dtGet
        'return the BindingSource to the calling method or control
        Return oBindingSource
    End Function

    Public Shared Sub HandleConnection(ByVal conn As SqlConnection)
        With conn
            'do a switch on the state of the connection
            Select Case .State
                Case ConnectionState.Open
                    'the connection is open
                    'close then re-open
                    .Close()
                    .Dispose()
                    Exit Select
                Case ConnectionState.Closed
                    'connection is open
                    'open the connection
                    .Open()
                    Exit Select
                Case Else
                    .Close()
                    .Open()
                    Exit Select
            End Select
        End With
    End Sub

    Public Shared Function InsertNewRecord(ByVal fields() As String, ByVal values() As String, ByVal procName As String) As Integer
        'Create the objects we need to insert a new record
        Using cnInsert As New SqlConnection(GetConnectionString("CnString"))
            'Now take care of the connection
            HandleConnection(cnInsert)
            Dim respuesta As Integer
            Dim cmdInsert As New SqlCommand
            Dim transaccion As SqlTransaction


            Dim sSQL As New String("")
            'Dim iSqlStatus As Integer

            transaccion = cnInsert.BeginTransaction("Insertando Clientes")

            'Set the stored procedure we're going to execute
            sSQL = procName

            'Inline sql needs to be structured like so
            'sSQL = "INSERT INTO YourTable(column1,column2,column3) VALUES('" & item1 & "','" & item2 & "','" & item3 & "')"

            'Clear any parameters
            cmdInsert.Parameters.Clear()
            Try
                'Set the SqlCommand Object Properties
                With cmdInsert
                    'Tell it what to execute
                    .CommandText = sSQL 'Your sql statement if using inline sql
                    'Tell it its a stored procedure
                    .CommandType = CommandType.StoredProcedure 'CommandType.Text for inline sql
                    Dim i As Integer
                    For i = 0 To UBound(fields)
                        .Parameters.AddWithValue(fields(i), values(i))
                    Next
                    i = Nothing
                    'Set the connection of the object
                    .Connection = cnInsert
                    .Transaction = transaccion
                End With
                'Set the iSqlStatus to the ExecuteNonQuery status of the insert (0 = success, 1 = failed)
                respuesta = cmdInsert.ExecuteScalar 'cmdInsert.ExecuteNonQuery()
                transaccion.Commit()
                Return respuesta
            Catch ex As Exception
                Try
                    transaccion.Rollback()
                Catch ex2 As Exception
                    Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType())
                    Console.WriteLine("  Message: {0}", ex2.Message)
                End Try
                Console.WriteLine("Error InsertNewRecord: " + ex.Message)
                Return -1
            Finally
                'Now close the connection
                HandleConnection(cnInsert)
            End Try
        End Using
    End Function

    Public Shared Function DeleteRecord(ByVal storeProc As String, ByVal id As Integer) As Boolean
        'Create the objects we need to insert a new record
        Dim cnDelete As New SqlConnection(GetConnectionString("CnString"))
        Dim cmdDelete As New SqlCommand
        Dim sSQL As New String("")
        Dim iSqlStatus As Integer

        'Set the stored procedure we're going to execute
        sSQL = storeProc

        'Inline sql needs to be structured like so
        'sSQL = "DELETE FROM YourTable WHERE YourID = " & id

        'Clear any parameters
        cmdDelete.Parameters.Clear()
        Try
            'Set the SqlCommand Object Properties
            With cmdDelete
                'Tell it what to execute
                .CommandText = sSQL 'Your sql statement if using inline sql
                'Tell it its a stored procedure
                .CommandType = CommandType.StoredProcedure 'CommandType.Text for inline sql
                'If you are indeed using a stored procedure
                'the next 3 lines pertain to you
                'Now add the parameters to our procedure
                'NOTE: Replace @value1.... with your parameter names in your stored procedure
                'and add all your parameters in this fashion
                .Parameters.AddWithValue("@codigo", id)
                'Set the connection of the object
                .Connection = cnDelete
            End With

            'Now take care of the connection
            HandleConnection(cnDelete)

            'Set the iSqlStatus to the ExecuteNonQuery 
            'status of the insert (0 = success, 1 = failed)
            iSqlStatus = cmdDelete.ExecuteNonQuery

            'Now check the status
            If Not iSqlStatus = -1 Then
                'DO your failed messaging here
                Return False
            Else
                'Do your success work here
                Return True
            End If
        Catch ex As Exception
            Console.WriteLine("Error DeleteRecord: " + ex.Message)
            Return False
        Finally
            'Now close the connection
            HandleConnection(cnDelete)
        End Try

    End Function

    Public Shared Function UpdateRecord(ByVal item1 As String, ByVal item2 As String, ByVal id As Integer) As Boolean
        'Create the objects we need to insert a new record
        Dim cnUpdate As New SqlConnection(GetConnectionString("YourConnName"))
        Dim cmdUpdate As New SqlCommand
        Dim sSQL As New String("")
        Dim iSqlStatus As Integer

        'Set the stored procedure we're going to execute
        sSQL = "YourProcName"

        'Inline sql needs to be structured like so
        'sSQL = "UPDATE YourTable SET column1 = '" & item1 & "',column2 = '" & item2 & "' WHERE YourId = " & id

        'Clear any parameters
        cmdUpdate.Parameters.Clear()
        Try
            'Set the SqlCommand Object Properties
            With cmdUpdate
                'Tell it what to execute
                .CommandText = sSQL 'Your sql statement if using inline sql
                'Tell it its a stored procedure
                .CommandType = CommandType.StoredProcedure 'CommandType.Text for inline sql
                'If you are indeed using a stored procedure
                'the next 3 lines pertain to you
                'Now add the parameters to our procedure
                'NOTE: Replace @value1.... with your parameter names in your stored procedure
                'and add all your parameters in this fashion
                .Parameters.AddWithValue("@value1", item1)
                .Parameters.AddWithValue("@value2", item2)
                .Parameters.AddWithValue("@YourID", id)
                'Set the connection of the object
                .Connection = cnUpdate
            End With

            'Now take care of the connection
            HandleConnection(cnUpdate)

            'Set the iSqlStatus to the ExecuteNonQuery 
            'status of the insert (0 = success, 1 = failed)
            iSqlStatus = cmdUpdate.ExecuteNonQuery

            'Now check the status
            If Not iSqlStatus = 0 Then
                'DO your failed messaging here
                Return False
            Else
                'Do your success work here
                Return True
            End If
        Catch ex As Exception
            Console.WriteLine("Error UpdateRecor: " + ex.Message)
        Finally
            'Now close the connection
            HandleConnection(cnUpdate)
        End Try
    End Function

    Public Shared Function GetRecordsByID(ByVal value As Integer) As BindingSource
        'The value that will be passed to the Command Object (this is a stored procedure)
        Dim sSQL As String = "YourProcName"
        'If using inline sql format is as such
        'sSQL = "SELECT value1,value2,value3 FROM YourTable WHERE YourValue = " & value
        'Stored procedure to execute
        Dim cnGetRecords As New SqlConnection(GetConnectionString("CnString"))
        'SqlConnection Object to use
        Dim cmdGetRecords As New SqlCommand()
        'SqlCommand Object to use
        Dim daGetRecords As New SqlDataAdapter()
        Dim dsGetRecords As New DataSet()
        'Clear any parameters
        cmdGetRecords.Parameters.Clear()
        Try
            With cmdGetRecords
                'set the SqlCommand Object Parameters
                .CommandText = sSQL
                'tell it what to execute
                .CommandType = CommandType.StoredProcedure
                'tell it its executing a Stored Procedure
                'heres the difference from the last method
                'here we are adding a parameter to send to our stored procedure
                'you use the AddWithValue, then the name of the parameter in your stored procedure
                'then the variable that holds that value
                .Parameters.AddWithValue("@year", value)
                'Set the Connection for the Command Object
                .Connection = cnGetRecords
            End With
            'set the state of the SqlConnection Object
            HandleConnection(cnGetRecords)
            'create BindingSource to return for our DataGrid Control
            Dim oBindingSource As BindingSource = GetBindingSource(cmdGetRecords)
            'now check to make sure a BindingSource was returned
            If Not oBindingSource Is Nothing Then
                'return the binding source to the calling method
                Return oBindingSource
            Else
                'no binding source was returned
                'let the user know the error
                Throw New Exception("There was no BindingSource returned")
                Return Nothing
            End If
        Catch ex As Exception
            MsgBox(ex.Message, , "Error Retrieving Data")
            Return Nothing
        Finally
            HandleConnection(cnGetRecords)
        End Try
    End Function

    Public Shared Function GetRecords(ByVal sSQL As String, ByVal cmdType As Byte) As BindingSource
        'The value that will be passed to the Command Object (this is a stored procedure)
        'If using inline sql format is as such
        'sSQL = "SELECT * FROM YourTable
        'Stored procedure to execute
        Dim cnGetRecords As New SqlConnection(GetConnectionString("CnString"))
        'SqlConnection Object to use
        Dim cmdGetRecords As New SqlCommand()
        'SqlCommand Object to use
        Dim daGetRecords As New SqlDataAdapter()
        Dim dsGetRecords As New DataSet()
        'Clear any parameters
        cmdGetRecords.Parameters.Clear()
        Try
            With cmdGetRecords
                'set the SqlCommand Object Parameters
                .CommandText = sSQL
                'tell it what to execute
                .CommandType = cmdType 'CommandType.StoredProcedure
                'Set the Connection for the Command Object
                .Connection = cnGetRecords
            End With
            'set the state of the SqlConnection Object
            HandleConnection(cnGetRecords)
            'create BindingSource to return for our DataGrid Control
            Dim oBindingSource As BindingSource = GetBindingSource(cmdGetRecords)
            'now check to make sure a BindingSource was returned
            If (oBindingSource IsNot Nothing) Then
                'return the binding source to the calling method
                Return oBindingSource
            Else
                'no binding source was returned
                'let the user know the error
                'Throw New Exception("There was no BindingSource returned")
                Return Nothing
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error al obtener Datos")
            Return Nothing
        Finally
            HandleConnection(cnGetRecords)
        End Try
    End Function

    Protected Overrides Sub Finalize()
        MyBase.Finalize()
    End Sub
End Class

